-- Drop existing database if exists
DROP DATABASE IF EXISTS trading_journal;

-- Create new database
CREATE DATABASE trading_journal CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE trading_journal;

-- Users table (for login)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','user') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin user (password: admin)
INSERT INTO users (username, password, role) VALUES 
('admin', MD5('admin'), 'admin');

-- Accounts table
CREATE TABLE accounts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    balance DECIMAL(12,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Symbols table
CREATE TABLE symbols (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Strategies table
CREATE TABLE strategies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Journal table
CREATE TABLE journal (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account_id INT NOT NULL,
    symbol_id INT NOT NULL,
    strategy_id INT NOT NULL,
    timeframe ENUM('15M','1H','4H','1D','1Week') NOT NULL,
    trade_type ENUM('Buy','Sell') NOT NULL,
    quantity DECIMAL(12,4) NOT NULL,
    tp DECIMAL(12,4) NOT NULL,
    sl DECIMAL(12,4) NOT NULL,
    rr_ratio DECIMAL(5,2) GENERATED ALWAYS AS (CASE WHEN sl != 0 THEN ABS((tp-sl)/sl) ELSE 0 END) STORED,
    profit_loss DECIMAL(12,2) NULL,
    percent_change DECIMAL(6,2) GENERATED ALWAYS AS (CASE WHEN profit_loss IS NOT NULL AND profit_loss <> 0 AND account_id IS NOT NULL THEN (profit_loss / (SELECT balance FROM accounts WHERE id=account_id)) * 100 ELSE NULL END) STORED,
    start_date DATE NOT NULL,
    close_date DATE NULL,
    follow_setup ENUM('Yes','No') NOT NULL DEFAULT 'Yes',
    emotions VARCHAR(255) NULL,
    comments TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (symbol_id) REFERENCES symbols(id) ON DELETE CASCADE,
    FOREIGN KEY (strategy_id) REFERENCES strategies(id) ON DELETE CASCADE
);

-- Optional: Insert some sample data for testing
INSERT INTO accounts (name, balance) VALUES 
('Main Account', 1000),
('Trading Account', 500);

INSERT INTO symbols (name) VALUES ('BTC'), ('ETH'), ('XAU'), ('CAD'), ('JPY');

INSERT INTO strategies (name) VALUES ('Scalping'), ('Swing'), ('Day Trading');
